import pandas as pd

# 读取 Excel 多个 sheet
file_path = "handled/四类文物统计.xlsx"
sheets = pd.read_excel(file_path, sheet_name=None)

results = []
newly_appeared = []

# 按照类型分析
for base_type in ["高钾", "铅钡"]:
    df_before = sheets[f"{base_type}无风化"]
    df_after = sheets[f"{base_type}风化"]

    # 设置 index 为 成分
    df_before = df_before.set_index("成分")
    df_after = df_after.set_index("成分")

    # 只保留均值列
    before_mean = df_before["均值"]
    after_mean = df_after["均值"]

    # 比例变化：(风化 - 无风化) / 无风化
    percent_change = (after_mean - before_mean) / before_mean * 100

    # 识别“从无到有”
    from_zero = (before_mean == 0) & (after_mean > 0)
    new_elements = after_mean[from_zero]
    newly_appeared.append(
        pd.DataFrame({
            "成分": new_elements.index,
            "风化均值": new_elements.values,
            "文物类型": base_type
        })
    )

    # 去除除以0导致的 inf/NaN
    percent_change = percent_change[~percent_change.index.isin(new_elements.index)]
    percent_change = percent_change.dropna()

    # 汇总结果
    sorted_change = percent_change.sort_values()
    result_df = pd.DataFrame({
        "成分": sorted_change.index,
        "变化百分比": sorted_change.values,
        "文物类型": base_type
    })
    results.append(result_df)

# 合并结果表格
all_change = pd.concat(results, ignore_index=True)
all_new = pd.concat(newly_appeared, ignore_index=True)

# 保存
all_change.to_excel("A/风化影响成分_百分比变化.xlsx", index=False)
all_new.to_excel("A/风化新增成分_从无到有.xlsx", index=False)
